﻿Imports System.Data.OleDb


Partial Public Class details
    Inherits System.Web.UI.Page
    Dim rate As Integer = 3

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
         
        If Not IsPostBack Then

            Dim hitCount As Integer = 0

            Dim conn = New OleDbConnection(ConfigurationManager.ConnectionStrings _
                    ("ConnectionString").ToString())

            Dim sqlCount As String = "SELECT VisitedCount FROM MaterialCollection WHERE ID = " + Request.QueryString("materialID").ToString()

            Dim findIDCmd As New OleDbCommand(sqlCount, conn)
            conn.Open()
            Dim dbread As OleDbDataReader = findIDCmd.ExecuteReader
            dbread.Read()
            hitCount = dbread("VisitedCount")

            hitCount = hitCount + 1
            conn.Close()

            Dim sql As String = "UPDATE MaterialCollection SET VisitedCount = " + hitCount.ToString() + " WHERE ID =  " + Request.QueryString("materialID").ToString()
            conn.Open()

            Dim dbcmd As New OleDbCommand(sqlCount, conn)
            dbcmd = New OleDbCommand(sql, conn)
            dbcmd.ExecuteNonQuery()
            conn.Close()

            Dim sqlRating As String = "SELECT Stars FROM MaterialCollection WHERE ID = " + Request.QueryString("materialID").ToString()

            Dim RatingIDCmd As New OleDbCommand(sqlRating, conn)
            conn.Open()
            Dim dbreadRating As OleDbDataReader = RatingIDCmd.ExecuteReader
            dbreadRating.Read()
            rate = dbreadRating("Stars")


            loadDiscussionDetail()
            Rating3.CurrentRating = getUserRating(conn, Session("materialID"), User.Identity.Name)
            Rating4.CurrentRating = getaverageuserrating(conn, Session("materialID"))
            conn.Close()
        End If



    End Sub


    'Protected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridView1.RowDataBound

    '    If e.Row.RowType = DataControlRowType.DataRow Then
    '        Dim radioButtonList As System.Web.UI.WebControls.RadioButtonList = CType(e.Row.FindControl("rblRating"), System.Web.UI.WebControls.RadioButtonList)
    '        radioButtonList.SelectedIndex = rate - 1
    '    End If

    'End Sub
 



    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click
        Dim id = Request.QueryString("ID")
        Dim conn = New OleDbConnection(ConfigurationManager.ConnectionStrings _
                    ("ConnectionString").ToString())
        conn.Open()
        id = Request.QueryString("materialID").ToString()

        For Each item As ListItem In CheckBoxList1.Items
            If item.Selected Then
                Dim sql As String = "INSERT INTO TagMaterialJunction (MaterialFkId,TagId) VALUES(@MaterialFkId,@TagId)"
                Dim cmd As New OleDbCommand(sql, conn)
                cmd.Parameters.AddWithValue("@MaterialFkId", id)
                cmd.Parameters.AddWithValue("@TagId", item.Value)
                cmd.ExecuteNonQuery()
            End If
        Next

        Dim stringArray() As String = txtTag.Text.Split(" ")

        For Each mystring As String In stringArray
            If mystring <> "" Then
                Dim newTagsSql As String = "Insert INTO Tags (TagName) VALUES (@TagName)"
                Dim newTagCmd As New OleDbCommand(newTagsSql, conn)
                newTagCmd.Parameters.AddWithValue("@TagName", mystring)
                newTagCmd.ExecuteNonQuery()

                Dim findIdSql As String = "SELECT TOP 1 ID From Tags ORDER BY ID Desc"
                Dim findIDCmd As New OleDbCommand(findIdSql, conn)
                Dim dbread As OleDbDataReader = findIDCmd.ExecuteReader
                dbread.Read()
                Dim idOfNewTag = dbread("ID")

                Dim tagJunctionSql As String = "INSERT INTO TagMaterialJunction (MaterialFkId,TagId) VALUES(@MaterialFkId,@TagId)"
                Dim tagJunctionCmd As New OleDbCommand(tagJunctionSql, conn)
                tagJunctionCmd.Parameters.AddWithValue("@MaterialFkId", id)
                tagJunctionCmd.Parameters.AddWithValue("@TagId", idOfNewTag)
                tagJunctionCmd.ExecuteNonQuery()
            End If
        Next
        conn.Close()
        Response.Redirect("http://localhost:1516/details.aspx?materialID=" + Session("materialID"))

    End Sub

    Protected Sub Button2_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button2.Click

        Dim dbconn = New OleDbConnection(ConfigurationManager.ConnectionStrings _
                           ("ConnectionString").ToString())
        dbconn.Open()
        If IsPostBack() Then
            Session("materialID") = Request.QueryString("materialID").ToString()
            
            'Rating4.CurrentRating = Rating3.CurrentRating
            rateMaterial(dbconn, User.Identity.Name, Rating3.CurrentRating, Session("materialID"))
        End If

        Rating3.CurrentRating = getUserRating(dbconn, Session("materialID"), User.Identity.Name)
        Rating4.CurrentRating = getaverageuserrating(dbconn, Session("materialID"))


        dbconn.Close()
    End Sub

    Protected Sub rateMaterial(ByVal dbconn As OleDbConnection, ByVal UserName As String, ByVal Rating3 As Integer, ByVal id1 As Integer)
        id1 = Request.QueryString("materialID").ToString()
        Dim previouslyaddedrate As String
        previouslyaddedrate = "Delete * from UserRatings where UserName=@UserName AND FK_MaterialName=@FK_MaterialName "
        Dim previouslyaddedDeletecmd As New OleDbCommand(previouslyaddedrate, dbconn)
        previouslyaddedDeletecmd.Parameters.AddWithValue("@UserName", UserName)
        previouslyaddedDeletecmd.Parameters.AddWithValue("@FK_MaterialName", id1)
        previouslyaddedDeletecmd.ExecuteNonQuery()

        Dim addRateSQL As String
        addRateSQL = "INSERT INTO UserRatings (UserName,FK_MaterialName,UserRate) VALUES (@UserName,@FK_MaterialName,@UserRate)"
        Dim addRatecmd As New OleDbCommand(addRateSQL, dbconn)
        addRatecmd.Parameters.AddWithValue("@UserName", UserName)
        addRatecmd.Parameters.AddWithValue("@FK_MaterialName", id1)
        addRatecmd.Parameters.AddWithValue("@UserRate", Rating3)
        addRatecmd.ExecuteNonQuery()

    End Sub

    Protected Function getaverageuserrating(ByVal dbconn As OleDbConnection, ByVal id1 As Integer)
        Dim averagesql As String = "SELECT AVG(UserRate) as ratingAverage FROM UserRatings where FK_MaterialName = " & id1
        Dim averagecmd As New OleDbCommand(averagesql, dbconn)
        Dim averageread = averagecmd.ExecuteReader()
        averageread.Read()
        Try
            Return CInt(averageread("ratingAverage"))
        Catch ex As Exception
            Return 0
        End Try
    End Function

    Protected Function getUserRating(ByVal dbconn As OleDbConnection, ByVal id1 As Integer, ByVal UserName As String)
        Dim usersql As String = "SELECT * FROM UserRatings where FK_MaterialName = " & id1 & " AND [UserName]='" & UserName & "'"
        Dim usersqlcmd As New OleDbCommand(usersql, dbconn)
        Dim usersqlReading = usersqlcmd.ExecuteReader()
        usersqlReading.Read()
        Try
            Return CInt(usersqlReading("UserRate"))
        Catch ex As Exception
            Return 0
        End Try


    End Function

    Protected Sub Button3_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button3.Click


        Dim conn2 = New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings _
               ("ConnectionString").ToString())
        conn2.Open()

        Dim sqlstr = "INSERT INTO DiscussionPosts ([Title],[Body],[MaterialTitle],[UserName],[PostedDate]) VALUES ([@Title],[@Body],[@MaterialTitle],[@UserName],[@PostedDate]) "
        Dim newPostsdbcmd As New OleDbCommand(sqlstr, conn2)
        newPostsdbcmd.Parameters.AddWithValue("@Title", txtTitle.Text)
        newPostsdbcmd.Parameters.AddWithValue("@Body", txtBody.Text)
        newPostsdbcmd.Parameters.AddWithValue("@MaterialTitle", Session("materialID"))
        newPostsdbcmd.Parameters.AddWithValue("@UserName", User.Identity.Name)
        newPostsdbcmd.Parameters.AddWithValue("@PostedDate", DateTime.Now.Date)

        newPostsdbcmd.ExecuteNonQuery()
        loadDiscussionDetail()



    End Sub

    Protected Function loadDiscussionDetail()
        Session("materialID") = Request.QueryString("materialID")

        Dim MaterialID = Request.QueryString("materialID") 'Request.Params(Session("materialID"))
        Dim conn1 = New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings _
                 ("ConnectionString").ToString())
        conn1.Open()

        Dim postingStr As String = "SELECT * FROM DiscussionPosts where [MaterialTitle]= @ID"
        Dim postingDBcmd As New OleDb.OleDbCommand(postingStr, conn1)
        postingDBcmd.Parameters.AddWithValue("@ID", Request.QueryString("materialID"))

        Dim dbread1 = postingDBcmd.ExecuteReader()
        Repeater1.DataSource = dbread1
        Repeater1.DataBind()
        Try
            Return (dbread1)
        Catch ex As Exception
            Return 0
        End Try
    End Function

    Protected Sub Repeater1_ItemCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.RepeaterCommandEventArgs) Handles Repeater1.ItemCommand

    End Sub



    Protected Sub GridView1_RowCommand(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCommandEventArgs) Handles GridView1.RowCommand
        If e.CommandName = "edit" Then

            Dim Downcount As Integer = 0

            Dim conn = New OleDbConnection(ConfigurationManager.ConnectionStrings _
                    ("ConnectionString").ToString())

            Dim sqlCount As String = "SELECT DownloadCount FROM MaterialCollection WHERE ID = " + Request.QueryString("materialID").ToString()

            Dim findIDCmd As New OleDbCommand(sqlCount, conn)
            conn.Open()
            Dim dbread As OleDbDataReader = findIDCmd.ExecuteReader
            dbread.Read()
            Downcount = dbread("DownloadCount")

            Downcount = Downcount + 1
            'conn.Close()

            Dim sql As String = "UPDATE MaterialCollection SET DownloadCount = " + Downcount.ToString() + " WHERE ID =  " + Request.QueryString("materialID").ToString()
            ' conn.Open()

            Dim dbcmd As New OleDbCommand(sqlCount, conn)
            dbcmd = New OleDbCommand(sql, conn)
            dbcmd.ExecuteNonQuery()
            'conn.Close()



            'Dim index As Integer = Convert.ToInt32(e.CommandArgument)
            Dim dd = "download.aspx?materialID=" & GridView1.DataKeys(0).Values("ID").ToString() & "&" & "file=" & GridView1.DataKeys(0).Values("File").ToString()
            Response.Redirect("download.aspx?materialID=" & GridView1.DataKeys(0).Values("ID").ToString() & "&" & "file=" & GridView1.DataKeys(0).Values("File").ToString())
        End If
    End Sub


End Class